package com.agency8x.model;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

import com.agency8x.bean.ResultInfoBean;
import com.agency8x.model.base.BaseWithdrawModel;
import com.agency8x.sqls.WithdrawSqls;
import com.agency8x.util.DateTimeUtil;
import com.agency8x.util.RegexUtil;
import com.jfinal.kit.StrKit;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.ICallback;
import com.jfinal.plugin.activerecord.Page;
import java.util.Date;

public class WithdrawModel extends BaseWithdrawModel<WithdrawModel> {

    public static final WithdrawModel me = new WithdrawModel().dao();

    /**
     * 所有 sql 与业务逻辑写在 Service 中，在此放在 Model 中仅为示例
     */
    public Page<WithdrawModel> paginate(int pageNumber, int pageSize) {
        return paginate(pageNumber, pageSize, WithdrawSqls.me.getPagingSelectSql(),
                WithdrawSqls.me.getPagingExceptSelectSql());
    }

    /**
     * 提现公共池
     */
    public Page<WithdrawModel> paginate(int pageNumber, int pageSize, String userId) {
        String sql = WithdrawSqls.me.getPagingSelectSql();
        String sqlFrom = WithdrawSqls.me.getPagingExceptSelectSql();
        sqlFrom += "where w.status=0 and w.auditor_id=0";
        if (userId != null && !"".equals(userId.trim())) {
            if (RegexUtil.isNumeric(userId)) {
                sqlFrom = sqlFrom + " and w.user_id = " + userId;
            }
        }
        sqlFrom = sqlFrom + " order by w.id desc";
        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    /**
     * 待审核
     */
    public Page<WithdrawModel> paginatePending(int pageNumber, int pageSize, String search_word, Integer auditor_id) {
        String sql = WithdrawSqls.me.getPagingSelectSql();
        String sqlFrom = WithdrawSqls.me.getPagingExceptSelectSql();
        sqlFrom += "where w.status=0";
        if (auditor_id > 0) {
            sqlFrom = sqlFrom + " and w.auditor_id = " + auditor_id;
        } else {
            sqlFrom = sqlFrom + " and w.auditor_id > 0 ";
        }
        if (RegexUtil.isNumeric(search_word) && StrKit.notBlank(search_word)) {
            sqlFrom = sqlFrom + " and w.user_id = " + search_word;
        }
        sqlFrom = sqlFrom + " order by w.id desc";
        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    /**
     * 审核记录
     *
     * @param endDate
     * @param beginDate
     */
    public Page<WithdrawModel> paginateHistory(int pageNumber, int pageSize, String searchword, Integer auditor_uid,
            Integer status, String beginDate, String endDate) {
        String sql = WithdrawSqls.me.getPagingSelectSql();
        String sqlFrom = WithdrawSqls.me.getPagingExceptSelectSql();
        sqlFrom += "where w.status= " + status;
        // if(id != 0 && id != null){//我的审核记录
        // sqlFrom = sqlFrom + " and w.auditor_id = "+id;
        // }

        if (DateTimeUtil.validateDate(beginDate)) {// 起始-终止
            sqlFrom = sqlFrom + " and w.create_time > '" + beginDate+ " 00:00:00'";
        }
        if (DateTimeUtil.validateDate(endDate)) {// 起始
            sqlFrom = sqlFrom + " and w.create_time < '" + beginDate + " 23:59:59'";
        }
        if (StrKit.notBlank(searchword)) {
        	if(RegexUtil.isNumeric(searchword)){
                sqlFrom = sqlFrom + " and (u.user_id like '%" + searchword + "%' or u.nickname like '%" + searchword + "%')";
        	}else{
                sqlFrom = sqlFrom + " and u.nickname like '%" + searchword + "%'";
        	}
        }
        sqlFrom = sqlFrom + " order by w.id desc";
        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    public List<WithdrawModel> paginateHistory1(int pageNumber, int pageSize, String searchword, Integer auditor_uid,
            Integer status, String beginDate, String endDate) {
        String sql = WithdrawSqls.me.getPagingSelectSql();
        String sqlFrom = WithdrawSqls.me.getPagingExceptSelectSql();
        sqlFrom += "where w.status= " + status;
        // if(id != 0 && id != null){//我的审核记录
        // sqlFrom = sqlFrom + " and w.auditor_id = "+id;
        // }
        if (DateTimeUtil.validateDate(beginDate)) {// 起始-终止
            sqlFrom = sqlFrom + " and w.create_time > '" + beginDate+ " 00:00:00'";
        }
        if (DateTimeUtil.validateDate(endDate)) {// 起始
            sqlFrom = sqlFrom + " and w.create_time < '" + beginDate + " 23:59:59'";
        }

        if (StrKit.notBlank(searchword)) {
            sqlFrom = sqlFrom + " and u.nickname like '%" + searchword + "%'";
        }
        sqlFrom = sqlFrom + " order by w.create_time desc";
        return this.find(sql + sqlFrom);
    }

    /**
     * 领取
     */
    public void getRecord(int auditor_id, String id) {
        String sql = WithdrawSqls.me.getUpdateAuditorIdSql()+" ("+id+")";
        Db.update(sql, auditor_id);
    }

    public ResultInfoBean auditWithdraw(Integer id, Integer auditor_uid, Integer status, String remark) {
        ResultInfoBean resultbean = new ResultInfoBean();
        Db.execute(new ICallback() {
            @Override
            public Object call(Connection conn) {
                try {
                    // 返回 1表示成功;0表示失败
                    CallableStatement proc = conn.prepareCall("{?=call proc_withdraw_audit(?,?,?,?,?)}");
                    proc.registerOutParameter(1, java.sql.Types.INTEGER);
                    proc.setInt(2, id);
                    proc.setInt(3, auditor_uid);
                    proc.setInt(4, status);
                    proc.setString(5, remark);
                    proc.registerOutParameter(6, java.sql.Types.VARCHAR);
                    proc.execute();
                    Integer userId = proc.getInt(1);
                    String strout = proc.getString(6);
                    resultbean.setResult(userId > 0);
                    resultbean.setMessage(strout);
                    resultbean.setObjectParam(userId);
                } catch (SQLException e) {
                    resultbean.setResult(false);
                    resultbean.setMessage(e.getMessage());
                }
                return null;
            }
        });
        return resultbean;
    }

    public void unAuditWithdraw(Integer id) {
    	/*
        ResultInfoBean resultbean = new ResultInfoBean();
        Db.execute(new ICallback() {
            @Override
            public Object call(Connection conn) {
                try {
                    // 返回 1表示成功;0表示失败
                    CallableStatement proc = conn.prepareCall("{?=call proc_withdraw_unaudit(?,?,?,?,?)}");
                    proc.registerOutParameter(1, java.sql.Types.INTEGER);
                    proc.setInt(2, id);
                    proc.setInt(3, auditor_uid);
                    proc.setInt(4, status);
                    proc.setString(5, remark);
                    proc.registerOutParameter(6, java.sql.Types.VARCHAR);
                    proc.execute();
                    Integer userId = proc.getInt(1);
                    String strout = proc.getString(6);
                    resultbean.setResult(userId > 0);
                    resultbean.setMessage(strout);
                    resultbean.setObjectParam(userId);
                } catch (SQLException e) {
                    resultbean.setResult(false);
                    resultbean.setMessage(e.getMessage());
                }
                return null;
            }
        });
        return resultbean;
        */
    }

    public WithdrawModel getAuditRecord(Integer id) {
        String sqlstmt = WithdrawSqls.me.getFindByIdSql();
        return this.findFirst(sqlstmt, id);
    }

    /**
     * 获取顶级代理所有下线的提现明细
     *
     * @param pageNumber
     * @param pageSize
     * @param top_agent_id
     * @param status
     * @param word
     * @param date1
     * @param date2
     * @return
     */
    public Page<WithdrawModel> getPaginateByTopAgentId(int pageNumber, int pageSize, Integer top_agent_id, Integer status, String word, String date1, String date2) {
        return getPaginateByUser(pageNumber, pageSize, "top_agent_id=" + top_agent_id, status, word, date1, date2);
    }

    public Page<WithdrawModel> getPaginateByUserId(int pageNumber, int pageSize, Integer user_id, Integer status, String word, String date1, String date2) {
        return getPaginateByUser(pageNumber, pageSize, "user_id=" + user_id, status, word, date1, date2);
    }

    public Page<WithdrawModel> getPaginateByParent1Uid(int pageNumber, int pageSize, Integer parent1_uid, Integer status, String word, String date1, String date2) {
        return getPaginateByUser(pageNumber, pageSize, "parent1_uid=" + parent1_uid, status, word, date1, date2);
    }

    public Page<WithdrawModel> getPaginateByParent2Uid(int pageNumber, int pageSize, Integer parent2_uid, Integer status, String word, String date1, String date2) {
        return getPaginateByUser(pageNumber, pageSize, "parent2_uid=" + parent2_uid, status, word, date1, date2);
    }

    private Page<WithdrawModel> getPaginateByUser(int pageNumber, int pageSize, String userCondition, Integer status, String word, String date1, String date2) {
        String sql = "SELECT u.user_id,u.mobile, u.nickname, w.id, w.amount, w.bank_name, w.account, w.holder, w.remark, w.rejected_remark, w.create_time, w.audit_time, w.auditor_id";
        String sqlFrom = "FROM dbo.t_finance_withdraw w LEFT JOIN dbo.t_user u ON w.user_id=u.user_id";
        String sqlWhere = " WHERE u." + userCondition + " and w.status=" + status;
        String sqlOrder = " ORDER BY w.id desc";
        if (StrKit.notBlank(date1) && DateTimeUtil.validateDate(date1)) {
            sqlWhere += " and w.create_time >='" + date1 + " 0:0:0'";
        }
        if (StrKit.notBlank(date2) && DateTimeUtil.validateDate(date2)) {
            sqlWhere += " and w.create_time <='" + date2 + " 23:59:59'";
        }

        if (StrKit.notBlank(word)) {
            sqlWhere += " and (u.user_id like '%" + word + "%' or u.nickname like '%" + word + "%')";
        }
        sqlFrom += sqlWhere + sqlOrder;
        return paginate(pageNumber, pageSize, sql, sqlFrom);
    }

    public String getCreateTimeText() {
        return DateTimeUtil.formatSqlServerDateTime(getCreateTime());
    }

    public String getAuditTimeText() {
        return DateTimeUtil.formatSqlServerDateTime(getAuditTime());
    }
}
